from datetime import datetime
import os
from openpyxl import load_workbook, Workbook
import logging
import re
from email import policy
from email.parser import BytesParser
from email.iterators import _structure

# 配置路径参数root_path3 = os.path.dirname(os.path.realpath(sys.executable))
CURRENT_DIR = os.path.dirname(os.path.abspath(__file__))   
# 文件路径配置
RESOURCE_FILE = os.path.join(CURRENT_DIR, "input_data.xlsx")
TEMPLATE_FILE = os.path.join(CURRENT_DIR, "template.xlsx")
timestamp = datetime.now().strftime("%Y%m%d_%H%M%S")
TARGET_FILE = os.path.join(CURRENT_DIR, f"output_result_{timestamp}.xlsx")
MAPPING_FILE = os.path.join(CURRENT_DIR, "mapping.txt")
FIXED_MAPPING_FILE = os.path.join(CURRENT_DIR, "fixed_mapping.txt")
EMAIL_MAPPING_FILE = os.path.join(CURRENT_DIR, "email_mapping.txt")
EMAIL_PATH = os.path.join(CURRENT_DIR, "email")
CONTAINER_TYPES = ["20GP", "40GP", "40HQ"]

# 配置日志记录
logging.basicConfig(level=logging.DEBUG, format='%(asctime)s - %(levelname)s - %(message)s',filename=os.path.join(CURRENT_DIR, "debug.log"))

def info(text):
    print(f"{text}")
    logging.info(f"{text}")

def debug(text):
    print(f"{text}")
    logging.debug(f"{text}")
def error(text):
    print(f"{text}")
    logging.error(f"{text}")

def validate_fields(new_row):
    # 获取承运人和主运单字段
    carrier = new_row.get("承运人")
    main_bill = str(new_row.get("主运单")).strip() 
    
    # 校验主运单字段
    if carrier == "HLCU":
        if not main_bill.startswith("HLCU"):
            raise ValueError(f"主运单 {main_bill} 不符合 HLCU 规则")
    elif carrier == "MAEU":
        if not main_bill.isdigit() or len(main_bill) != 9:
            raise ValueError(f"主运单 {main_bill} 不符合 MAEU 规则")
    elif carrier == "CMDU":
        if not (main_bill[:3].isalpha() and main_bill[3:].isdigit()):
            raise ValueError(f"主运单 {main_bill} 不符合 CMDU 规则")
def process_fields(row_data):
    """处理源行集装箱类型，对源字段执行校验和特殊处理，最终返回需要生成的新行数据列表"""
    rows_to_add = []
    # 遍历特殊字段列表，查询有大于0的集装箱类型字段
    for field in CONTAINER_TYPES:
        value = row_data.get(field, 0)
        try:
            quantity = int(float(value)) if value not in (None, "") else 0
            if quantity > 0:
                for _ in range(quantity):
                    new_row = row_data.copy()
                    new_row["集装箱类型"] = field
                    # 处理起运港字段（值为 "TAO/孙" 时替换为 "TAO"）
                    if "起运港" in new_row:
                        new_row["起运港"] = new_row["起运港"].split('/')[0]
                    validate_fields(new_row)
                    rows_to_add.append(new_row)
        except (ValueError, TypeError) as e:
            print(f"根据原行数据，生成新行数据时发生错误: {str(e)}")
            raise
    return rows_to_add
def sort_generated_rows(generated_rows, ns_output, column_mapping, fixed_mapping):
    try:
        ordered_rows = [] 
        for row in generated_rows:
            output_row = {}
            # 字段映射
            for src_col, dest_col in column_mapping.items():
                output_row[dest_col] = row.get(src_col)
            # 模板值映射
            for dest_col, fixed_value in fixed_mapping.items():
                output_row[dest_col] = fixed_value
            # 集装箱类型：20GP、40GP、40HQ
            output_row["集装箱类型"] = row["集装箱类型"]
            # 按模板列顺序构建数据
            ordered_row = [
                output_row.get(header.value, None)
                for header in ns_output[1]
            ]
            ordered_rows.append(ordered_row)  # 添加到列表中
        return ordered_rows
    except Exception as e:
        error(f"❌ 构建新行数据失败: {str(e)}")
        raise

def process_resource_row(row_data, ns_output, column_mapping, fixed_mapping):
    try:
        """处理源行数据，生成新行数据并返回"""
        generated_rows = process_fields(row_data)
        sorted_rows = sort_generated_rows(generated_rows, ns_output, column_mapping, fixed_mapping)
        info(f"PO号：{row_data['PO号']}处理完成......")
        return sorted_rows
    except Exception as e:
        error(f"❌ 处理源行数据失败: {str(e)}")
        raise

def process_resource_data(ns_output, rs_input, column_mapping, fixed_mapping):
    new_rows = [] 
    info("✅ 开始处理资源数据...")
    try:
        index = 1
        # 读取表头并去除末尾空格
        input_headers = [
            str(cell.value).rstrip()  # 关键修改点
            for cell in rs_input[1]
        ]
        # 逐行处理源数据
        for row in rs_input.iter_rows(min_row=2):
            row_data = {
                input_headers[idx]: cell.value
                for idx, cell in enumerate(row)
            }
            new_rows.extend(process_resource_row(row_data, ns_output, column_mapping, fixed_mapping))
            index += 1
        info(f"✅ 处理完成！共生成 {len(new_rows)} 行数据")
        return new_rows
    except Exception as e:
        error(f"❌ 处理第{index}行数据失败: {str(e)}")
        raise

def create_newfile_by_template():
    """创建新文件，并复制模板表头"""
    # 加载模板并获取表头（第一行数据）
    header_row = next(load_workbook(TEMPLATE_FILE).active.iter_rows(max_row=1, values_only=True))  # 提取第一行数据
    
    # 创建新工作簿并写入表头
    new_wb = Workbook()
    new_sheet = new_wb.active
    new_sheet.append(header_row)
    
    new_wb.save(TARGET_FILE)
    return new_wb
def read_mapping(mapping_file):
    """读取映射文件，返回字典"""
    mapping = {}
    with open(mapping_file, 'r', encoding='utf-8') as f:
        for line in f:
            line = line.strip()
            if line and ':' in line:
                key, value = line.split(':', 1)
                mapping[key.strip()] = value.strip()
    return mapping
def parse_eml_files(email_folder):
    """
    解析邮件文件夹，返回结构：{PO号: {字段映射字典}}
    使用标准库实现，不依赖第三方库
    """
    # 读取邮件字段映射关系
    email_mapping = read_mapping(EMAIL_MAPPING_FILE)
    
    po_field_mapping = {}

    for filename in os.listdir(email_folder):
        if not filename.lower().endswith('.eml'):
            continue

        # 从文件名提取PO号
        po_match = re.search(r'Nº (\d+)', filename)
        if not po_match:
            continue
        po_number = po_match.group(1)

        # 解析邮件内容
        eml_path = os.path.join(email_folder, filename)
        with open(eml_path, 'rb') as f:
            msg = BytesParser(policy=policy.default).parse(f)
            
            # 获取邮件正文（优先选择text/html部分）
            body = ""
            for part in msg.walk():
                content_type = part.get_content_type()
                if content_type == 'text/html':
                    body = part.get_content()
                    break
            
            # 如果没有HTML正文，尝试获取纯文本
            if not body and msg.is_multipart():
                for part in msg.walk():
                    if part.get_content_type() == 'text/plain':
                        body = part.get_content()
                        break
            
            # 提取字段值
            field_values = {}
            for eml_field, target_field in email_mapping.items():
                # 表格解析正则（匹配字段名和相邻单元格）
                pattern = re.compile(
                    r'<td[^>]*>\s*%s\s*</td>\s*<td[^>]*>(.*?)</td>' % re.escape(eml_field),
                    re.IGNORECASE | re.DOTALL
                )
                match = pattern.search(body)
                if match:
                    # 清除HTML标签并去除空白
                    clean_value = re.sub(r'<.*?>', '', match.group(1)).strip()
                    field_values[target_field] = clean_value

            po_field_mapping[po_number] = field_values

    return po_field_mapping
def main():
    try:
        # 读取两种映射关系：字段名映射和模板值映射
        column_mapping = read_mapping(MAPPING_FILE)   # 字段名映射
        fixed_mapping = read_mapping(FIXED_MAPPING_FILE)   # 模板值映射
        info("📋 读取映射文件成功！")
    except Exception as e:
        error(f"❌ 映射文件读取失败: {str(e)}")
    try:
        # 获取新工作簿和输出工作表
        nf_output = create_newfile_by_template()
        ns_output = nf_output.active
        info("📋 创建目标文件成功！")
        # 仅读取源数据的值/表头
        rs_input = load_workbook(RESOURCE_FILE, data_only=True).active
        info("📋 读取源数据成功！")
    except Exception as e:
        error(f"❌ 获取数据失败: {str(e)}")
        if os.path.exists(TARGET_FILE):
            os.remove(TARGET_FILE)
            debug("❌ 获取数据失败，已删除目标文件。")
            
    try:
        new_rows = process_resource_data(ns_output, rs_input, column_mapping, fixed_mapping)
        info("✅ 源数据处理完成！")
    except Exception as e:
        error(f"❌ 源数据处理失败: {str(e)}")
        if os.path.exists(TARGET_FILE):
            os.remove(TARGET_FILE)
            debug("❌ 源数据处理失败，已删除目标文件。")
    try:
        list(map(lambda row: ns_output.append(row), new_rows))
        nf_output.save(TARGET_FILE)
        info(f"🎉 结果已保存至: {TARGET_FILE}")
    except Exception as e:
        error(f"❌ 结果保存失败: {str(e)}")
        if os.path.exists(TARGET_FILE):
            os.remove(TARGET_FILE)
            debug("❌ 结果保存失败，已删除目标文件。")

if __name__ == "__main__":
    main()